package com.company.ems.dao;

import com.company.ems.model.Employee;
import com.company.ems.model.User;
import com.company.ems.util.HikariCPDataSource;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDAOImpl implements EmployeeDAO {

    @Override
    public List<Employee> getAllEmployees() {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT e.*, u.username as creator_name FROM employees e LEFT JOIN users u ON e.created_by = u.id ORDER BY e.id";

        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                Employee emp = mapResultSetToEmployee(rs);
                employees.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    @Override
    public Employee getEmployeeById(int id) {
        String sql = "SELECT e.*, u.username as creator_name FROM employees e LEFT JOIN users u ON e.created_by = u.id WHERE e.id = ?";
        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToEmployee(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Employee> searchEmployees(String name, String department, String position,
                                          LocalDate hireDateFrom, LocalDate hireDateTo) {
        StringBuilder sql = new StringBuilder(
                "SELECT e.*, u.username as creator_name " +
                        "FROM employees e LEFT JOIN users u ON e.created_by = u.id " +
                        "WHERE 1=1"
        );
        List<Object> params = new ArrayList<>();

        if (name != null && !name.trim().isEmpty()) {
            sql.append(" AND e.name LIKE ?");
            params.add("%" + name.trim() + "%");
        }
        if (department != null && !department.trim().isEmpty()) {
            sql.append(" AND e.department = ?");
            params.add(department.trim());
        }
        if (position != null && !position.trim().isEmpty()) {
            sql.append(" AND e.position = ?");
            params.add(position.trim());
        }
        if (hireDateFrom != null) {
            sql.append(" AND e.hire_date >= ?");
            params.add(Date.valueOf(hireDateFrom));
        }
        if (hireDateTo != null) {
            sql.append(" AND e.hire_date <= ?");
            params.add(Date.valueOf(hireDateTo));
        }

        sql.append(" ORDER BY e.id");

        List<Employee> employees = new ArrayList<>();
        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    employees.add(mapResultSetToEmployee(rs));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    // EmployeeDAOImpl.java 中添加此私有方法
    public void appendConditions(StringBuilder sql, List<Object> params,
                                 String name, String department, String position,
                                 LocalDate hireDateFrom, LocalDate hireDateTo) {

        if (name != null && !name.trim().isEmpty()) {
            sql.append(" AND e.name LIKE ?");
            params.add("%" + name.trim() + "%");
        }
        if (department != null && !department.trim().isEmpty()) {
            sql.append(" AND e.department LIKE ?");
            params.add("%" + department.trim() + "%");
        }
        if (position != null && !position.trim().isEmpty()) {
            sql.append(" AND e.position LIKE ?");
            params.add("%" + position.trim() + "%");
        }
        if (hireDateFrom != null) {
            sql.append(" AND e.hire_date >= ?");
            params.add(hireDateFrom);
        }
        if (hireDateTo != null) {
            sql.append(" AND e.hire_date <= ?");
            params.add(hireDateTo);
        }
    }

    @Override
    public List<Employee> searchEmployeesWithPagination(String name, String department, String position,
                                                        LocalDate hireDateFrom, LocalDate hireDateTo,
                                                        int page, int recordsPerPage) {
        StringBuilder sql = new StringBuilder(
                "SELECT e.*, u.username as creator_name FROM employees e LEFT JOIN users u ON e.created_by = u.id WHERE 1=1"
        );
        List<Object> params = new ArrayList<>();
        appendConditions(sql, params, name, department, position, hireDateFrom, hireDateTo);
        sql.append(" ORDER BY e.id LIMIT ? OFFSET ?");
        params.add(recordsPerPage);
        params.add((page - 1) * recordsPerPage);

        List<Employee> employees = new ArrayList<>();
        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    employees.add(mapResultSetToEmployee(rs));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employees;
    }

    public int countSearchResults(String name, String department, String position,
                                  LocalDate hireDateFrom, LocalDate hireDateTo) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM employees e WHERE 1=1");
        List<Object> params = new ArrayList<>();
        appendConditions(sql, params, name, department, position, hireDateFrom, hireDateTo);

        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    @Override
    public boolean addEmployee(Employee employee) {
        String sql = "INSERT INTO employees (name, department, position, salary, email, phone, hire_date, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setString(1, employee.getName());
            stmt.setString(2, employee.getDepartment());
            stmt.setString(3, employee.getPosition());
            stmt.setDouble(4, employee.getSalary());
            stmt.setString(5, employee.getEmail());
            stmt.setString(6, employee.getPhone());
            stmt.setDate(7, Date.valueOf(employee.getHireDate()));
            stmt.setInt(8, employee.getCreatedBy().getId());

            int rows = stmt.executeUpdate();
            if (rows > 0) {
                try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        employee.setId(generatedKeys.getInt(1));
                    }
                }
            }
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updateEmployee(Employee employee) {
        String sql = "UPDATE employees SET name=?, department=?, position=?, salary=?, email=?, phone=?, hire_date=? WHERE id=?";
        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, employee.getName());
            stmt.setString(2, employee.getDepartment());
            stmt.setString(3, employee.getPosition());
            stmt.setDouble(4, employee.getSalary());
            stmt.setString(5, employee.getEmail());
            stmt.setString(6, employee.getPhone());
            stmt.setDate(7, Date.valueOf(employee.getHireDate()));
            stmt.setInt(8, employee.getId());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean deleteEmployee(int id) {
        String sql = "DELETE FROM employees WHERE id = ?";
        try (Connection conn = HikariCPDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 将 ResultSet 映射为 Employee 对象
    private Employee mapResultSetToEmployee(ResultSet rs) throws SQLException {
        Employee emp = new Employee();
        emp.setId(rs.getInt("id"));
        emp.setName(rs.getString("name"));
        emp.setDepartment(rs.getString("department"));
        emp.setPosition(rs.getString("position"));
        emp.setSalary(rs.getDouble("salary"));
        emp.setEmail(rs.getString("email"));
        emp.setPhone(rs.getString("phone"));
        emp.setHireDate(rs.getDate("hire_date").toLocalDate());
        emp.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        emp.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());

        // 设置创建人信息
        User creator = new User();
        creator.setId(rs.getInt("created_by"));
        creator.setUsername(rs.getString("creator_name"));
        emp.setCreatedBy(creator);

        return emp;
    }
}